package com.dp.service.impl;
import java.util.ArrayList;
import java.util.List;

import javax.persistence.Query;

import org.springframework.stereotype.Service;

import com.dp.service.impl3.BaseImpl;
import com.dp.vo.TAreainfo;
import com.dp.vo.TPhonenoseg;
import com.dp.vo.TSysSysparam;


@Service
public class SysServiceImpl extends BaseImpl {
    

    @SuppressWarnings("unchecked")
    public List<TSysSysparam> findAllSysparam(String fn,String ln) {
    	List<TSysSysparam> s=new ArrayList<TSysSysparam>();
    	try {
    	
    		  Query query = em.createQuery("select p FROM TSysSysparam p" +
    	        		" where   p.tSysSysparamPK.pfirstname like '%"+(fn!=null?fn:"")+"%' " +
    	        				" and p.tSysSysparamPK.plastname like '%"+(ln!=null?ln:"")+"%'    order by p.tSysSysparamPK.pfirstname,p.tSysSysparamPK.plastname ");
    	     
    	        s=query.getResultList();    
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			 // this.closeAll();
		}
          
        return s;
    }
    public List<TAreainfo> findAllAreainfo() {
    	List<TAreainfo> s=new ArrayList<TAreainfo>();
    	try {
    
        Query query = em.createQuery("select p FROM TAreainfo p "+
        		" order by p.upareaid,p.areaid");        		
        	s=query.getResultList();  
    	} catch (Exception e) {
			e.printStackTrace();
		}
        return s;
    }
    public List<TAreainfo> findAreainfo(String upid) {
    	List<TAreainfo> s=new ArrayList<TAreainfo>();
    	try{
    	
        Query query = em.createQuery("select p FROM TAreainfo p where p.areaid= "+upid+
        		" order by p.upareaid,p.areaid ");        		
        	s=query.getResultList();        
    	} catch (Exception e) {
			e.printStackTrace();
		}
        return s;
    }
    
    public TPhonenoseg findPhonenoseg(String p) {
    	  
    	TPhonenoseg s=null;
        try{
        	Query query = em.createQuery("select p FROM TPhonenoseg p" +
        		" where   p.tPhonenosegPK.phonenoseg= '"+(p!=null?p:"")+"' ");
        	s=(TPhonenoseg)query.getSingleResult(); 
        }catch(Exception e){
        	e.printStackTrace();
        }
        
        return s;
    }
    public List<List<String>> findAllPhonenoseg(String p) {
    	List<List<String>>total=new ArrayList<List<String>>();	     
    	List<String>   temp=null;
    	try {
//			this.init();
		
    	this.sql="select e.phonenoseg," +
    			" case e.carriertype when 1 then '联通' when 2 then '移动' else '电信' end as carrier," +
    			"  e.areaname,case e.upareaid when 0 then e.areaname else c.areaname end as p" +
    			"   , e.areaid,e.carriertype	" +
    			" from ( 	select a.areaid,a.carriertype,a.phonenoseg,b.areaname,b.upareaid from t_phonenoseg a,t_areainfo b" +
    			"    	where a.phonenoseg like '%"+p+"%' and a.areaid=b.areaid " +
    			"    	) e  left join t_areainfo c on( e.upareaid=c.areaid) ";
//    	this.rs1=stmt.executeQuery(sql);
//    	int col=rs1.getMetaData().getColumnCount();
    	List<Object[]> tempp=this.em.createNativeQuery(sql).getResultList();
		for(Object[]o:tempp)
		{
    		temp=new ArrayList<String>();
    		for(int i=0;i<6;i++) temp.add(this.getString(o[i]));    	  
    		total.add(temp);
    	}    	
    	
    	} catch (Exception e) {
			e.printStackTrace();
		}
        return total;
    }
    public void findSysparam(TSysSysparam ts) {
    	try {
    	TSysSysparam t=null;
    	
    	   t=em.find(TSysSysparam.class, ts.getTSysSysparamPK());
    	   ts.setPvalue(t.getPvalue());
    	   ts.setPdesc(t.getPdesc());
    	} catch (Exception e) {
			e.printStackTrace();
		}
    }
    public void deleteSysparam(TSysSysparam ts) {
    	try {
    	
    	 em.remove(em.find(TSysSysparam.class, ts.getTSysSysparamPK()));
   	     em.flush();       
    	} catch (Exception e) {
    		e.printStackTrace();
    	}
    }
    
    public void update(Class o,Object pk,Object n) {
    	
    	 em.remove(em.find(o, pk));
    	 em.flush();
    	 em.persist(n);
    	 em.flush();        
    	 
    }
    
    public void saveSysparam(TSysSysparam ts) {
    	try { 	
    	 em.persist(ts);
    	 em.flush();        
    	} catch (Exception e) {
			e.printStackTrace();
		}       
    }
    /*public void updateSysparam(TSysSysparam o,TSysSysparam n) {
    	
    	try { 	
    	 emr.remove(emr.find(TSysSysparam.class, o.getTSysSysparamPK()));
    	 emr.flush();
    	 emr.persist(n);
    	 emr.flush();        
    	} catch (Exception e) {
			e.printStackTrace();
		}     
    }*/

}